﻿using System.Text;
using PPI.Entities;
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using PPI.Entities.Enumerator;
using PPI.Entities.Lookup;
using System.Data;
using System.IO;
using PPI.Entities.Settings.Mobile;
using PPI.Entities.Utility;
using PPI.Entities.XML;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.Client.Logic
{
    public class LVendor_Visit_XMissionStructure
    {
        #region Query

        public static List<EVendor_Visit_XMissionStructure> GetServerVisits()
        {
            DataTable _Datatable = WebConsumer.WVendor_Visit_XMissionStructure.GetServerVisits().Tables[0];
            List<EVendor_Visit_XMissionStructure> _StructureList = ParseDatatableVisit(_Datatable);
            return _StructureList;



        }

        public static List<EVendor_Visit_XMissionStructure> GetServerVisitById(Int32 visitId)
        {
            DataTable dtVisit = WebConsumer.WVendor_Visit_XMissionStructure.GetServerVisitsByID(visitId).Tables[0];
            DataTable dtImages = WebConsumer.WVendor_Visit_XMissionStructures_Image.GetByStructureID(visitId).Tables[0];
            return ParseDatatableVisit(dtVisit, dtImages);
        }

        private static List<EVendor_Visit_XMissionStructure> ParseDatatableVisit(DataTable _Datatable, List<EVendor_Visit_XMissionStructures_Image> imageList)
        {
            Int32 StructureIdFlag = Int32.MinValue;
            Int32 _CurrentStructureID;

            EVendor_Visit_XMissionStructure _Structure;
            List<EVendor_Visit_XMissionStructure> _StructureList = new List<EVendor_Visit_XMissionStructure>();

            EVendor_Visit_XMissionStructures_Pole _Pole;
            
            List<EVendor_Visit_XMissionStructures_Pole> _PoleList = new List<EVendor_Visit_XMissionStructures_Pole>();

            DataRow _DataRow = null;
            DataRow _PreviusRow = null;
            for (int i = 0; i < _Datatable.Rows.Count; i++)
            {
                _DataRow = _Datatable.Rows[i];
                if (i != 0)
                    _PreviusRow = _Datatable.Rows[i - 1];

                _CurrentStructureID = ConvertHelper.ToInt32(_DataRow["Vendor_Visit_XMissionStructureId"]);


                if (StructureIdFlag != Int32.MinValue)
                {
                    //Do control break
                    if (StructureIdFlag != _CurrentStructureID)
                    {
                        _Structure = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]));
                        _Structure.Altitude = ConvertHelper.Todouble(_PreviusRow["Structure_Altitude"]);
                        _Structure.ServerID = ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]);
                        _Structure.Longitude = ConvertHelper.Todouble(_PreviusRow["Structure_Longitude"]);
                        _Structure.Latitude = ConvertHelper.Todouble(_PreviusRow["Structure_Latitude"]);
                        _Structure.SatelliteCount = ConvertHelper.Toint(_PreviusRow["Structure_SatelliteCount"]);
                        _Structure.SatelliteTime = ConvertHelper.ToDateTime(_PreviusRow["Structure_SatelliteTime"]);
                        _Structure.SolutionAvailable = ConvertHelper.Tobool(_PreviusRow["Structure_SolutionAvailable"]);
                        _Structure.Date = ConvertHelper.ToDateTime(_PreviusRow["Date"]);
                        _Structure.Vendor_Visit_Type.Name = ConvertHelper.Tostring(_PreviusRow["visit_Name"]);
                        _Structure.Vendor_Visit_Type.Vendor_Visit_TypeId = ConvertHelper.Toint(_PreviusRow["Vendor_Visit_TypeFK"]);
                        foreach (EBasePole p in _PoleList)
                        {
                            if (!_Structure.Poles.Contains(p))
                            {
                                p.Asset = _Structure;
                                _Structure.Poles.Add(p);
                            }
                                
                        }
                        _Structure.Vendor_Project = new EVendor_Project(ConvertHelper.Toint(_PreviusRow["Vendor_ProjectFK"]), ConvertHelper.Tostring(_PreviusRow["vendor_Name"]));
                        _Structure.Vendor_Project.Vendor = new EVendor(ConvertHelper.Toint(_PreviusRow["Vendor_Id"]), ConvertHelper.Tostring(_PreviusRow["Company_Name"]), "");
                        _Structure.Vendor_Contact = new EVendor_Contact(ConvertHelper.ToInt32(_PreviusRow["vendor_Contact_Id"]));
                        _Structure.Vendor_Contact.Name = ConvertHelper.Tostring(_PreviusRow["Vendor_Contact_Name"]);
                        //_Structure.Handheld = ConvertHelper.Tostring(_PreviusRow["Handheld"]);
                        LVendor_Visit_XMissionStructures_Image.AssignToAsset(_Structure, imageList);
                        _Structure.TopType = new EXMissionStructures_TopType(ConvertHelper.Toint(_PreviusRow["XMissionStructures_TopTypeFk"]), ConvertHelper.Tostring(_PreviusRow["TopType_Name"]));
                        _StructureList.Add(_Structure);
                        StructureIdFlag = _CurrentStructureID;
                        _PoleList = new List<EVendor_Visit_XMissionStructures_Pole>();
                    }
                }

                // Fill Poles Entities
                if (ConvertHelper.ToInt32(_DataRow["Pole_ID"]) != Int32.MinValue)
                {
                    _Pole = new EVendor_Visit_XMissionStructures_Pole();
                    _Pole.Altitude = ConvertHelper.Todouble(_DataRow["Pole_Altitude"]);
                    _Pole.Latitude = ConvertHelper.Todouble(_DataRow["Pole_Latitude"]);
                    _Pole.Longitude = ConvertHelper.Todouble(_DataRow["Pole_Longitude"]);
                    _Pole.Id = ConvertHelper.ToInt32(_DataRow["Pole_ID"]);
                    _Pole.ServerID = ConvertHelper.ToInt32(_DataRow["Pole_ID"]);
                    _Pole.SatelliteTime = ConvertHelper.ToDateTime(_DataRow["Pole_SatelliteTime"]);
                    _Pole.SatelliteCount = ConvertHelper.Toint(_DataRow["Pole_SatelliteCount"]);
                    _Pole.SolutionAvailable = ConvertHelper.Tobool(_DataRow["Pole_SolutionAvailable"]);
                    _Pole.Asset = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_DataRow["Vendor_Visit_XMissionStructureId"]));
                    LVendor_Visit_XMissionStructures_Image.AssignToPole(_Pole, imageList);
                    _Pole.Year = ConvertHelper.Toint(_DataRow["Pole_Year"]);
                    _Pole.AID = ConvertHelper.Tostring(_DataRow["Pole_AID"]);
                    _Pole.LVisit = ConvertHelper.Toint(_DataRow["Pole_LVisit"]);
                    _Pole.Number = ConvertHelper.Tostring(_DataRow["Pole_Number"]);
                    _Pole.Length = ConvertHelper.Todouble(_DataRow["Pole_Length"]);
                    _Pole.LVisitor = ConvertHelper.Tostring(_DataRow["Pole_LVisitor"]);

                    _Pole.Class = new EXMissionStructures_PoleClass(ConvertHelper.Toint(_DataRow["Pole_ClassID"]), ConvertHelper.Tostring(_DataRow["Classes_Name"]));
                    _Pole.TopType = new EXMissionStructures_PoleTopType(ConvertHelper.Toint(_DataRow["Pole_TopID"]), ConvertHelper.Tostring(_DataRow["Top_Name"]));
                    _Pole.Species = new EXMissionStructures_PoleSpecies(ConvertHelper.Toint(_DataRow["Pole_SpeciesID"]), ConvertHelper.Tostring(_DataRow["Species_Name"]));
                    _Pole.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.Toint(_DataRow["Pole_TreatmentID"]), ConvertHelper.Tostring(_DataRow["Treatment_Name"]));

                    _PoleList.Add(_Pole);
                }

                if (StructureIdFlag == Int32.MinValue)
                    StructureIdFlag = _CurrentStructureID;


            }
            if (_Datatable.Rows.Count > 0)
            {
                _PreviusRow = _Datatable.Rows[_Datatable.Rows.Count - 1];
                //Add last structure
                _Structure = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]));
                _Structure.Altitude = ConvertHelper.Todouble(_PreviusRow["Structure_Altitude"]);
                _Structure.ServerID = ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]);
                _Structure.Longitude = ConvertHelper.Todouble(_PreviusRow["Structure_Longitude"]);
                _Structure.Latitude = ConvertHelper.Todouble(_PreviusRow["Structure_Latitude"]);
                _Structure.SatelliteCount = ConvertHelper.Toint(_PreviusRow["Structure_SatelliteCount"]);
                _Structure.SatelliteTime = ConvertHelper.ToDateTime(_PreviusRow["Structure_SatelliteTime"]);
                _Structure.SolutionAvailable = ConvertHelper.Tobool(_PreviusRow["Structure_SolutionAvailable"]);
                _Structure.Date = ConvertHelper.ToDateTime(_PreviusRow["Date"]);
                _Structure.Vendor_Project = new EVendor_Project(ConvertHelper.Toint(_PreviusRow["Vendor_ProjectFK"]), ConvertHelper.Tostring(_PreviusRow["vendor_Name"]));
                _Structure.Vendor_Project.Vendor = new EVendor(ConvertHelper.Toint(_PreviusRow["Vendor_Id"]), ConvertHelper.Tostring(_PreviusRow["Company_Name"]), "");
                _Structure.Vendor_Contact = new EVendor_Contact(ConvertHelper.ToInt32(_PreviusRow["vendor_Contact_Id"]));
                _Structure.Vendor_Contact.Name = ConvertHelper.Tostring(_PreviusRow["Vendor_Contact_Name"]);
                _Structure.Vendor_Visit_Type.Name = ConvertHelper.Tostring(_PreviusRow["visit_Name"]);
                _Structure.Vendor_Visit_Type.Vendor_Visit_TypeId = ConvertHelper.Toint(_PreviusRow["Vendor_Visit_TypeFK"]);

                //_Structure.Handheld = ConvertHelper.Tostring(_PreviusRow["Handheld"]);

                _Structure.TopType = new EXMissionStructures_TopType(ConvertHelper.Toint(_PreviusRow["XMissionStructures_TopTypeFk"]), ConvertHelper.Tostring(_PreviusRow["TopType_Name"]));
                LVendor_Visit_XMissionStructures_Image.AssignToAsset(_Structure, imageList);
                foreach (EBasePole p in _PoleList)
                {
                    if (!_Structure.Poles.Contains(p))
                    {
                        p.Asset = _Structure;
                        _Structure.Poles.Add(p);
                    }
                }
                _StructureList.Add(_Structure);
            }
            return _StructureList;
        }

        private static List<EVendor_Visit_XMissionStructure> ParseDatatableVisit(DataTable dt)
        {
            return ParseDatatableVisit(dt, new List<EVendor_Visit_XMissionStructures_Image>());
        }

        private static List<EVendor_Visit_XMissionStructure> ParseDatatableVisit(DataTable dtVisit, DataTable dtImage)
        {
            List<EVendor_Visit_XMissionStructures_Image> imageList =
                LVendor_Visit_XMissionStructures_Image.ParseDatatableImage(dtImage);

            return ParseDatatableVisit(dtVisit, imageList);
        }

        public static List<EVendor_Visit_XMissionStructure> GetServerVisitsWithOutUtility(string connectionKey)
        {
            return GetServerVisitsByUtility(Int32.MinValue, connectionKey);
        }

        public static List<EVendor_Visit_XMissionStructure> GetServerVisitsByUtility(Int32 utlityID, string connectionKey)
        {
            //DataTable _Datatable = WVendor_Visit_XMissionStructure.GetServerVisitsByUtilityID(utlityID).Tables[0];
            DataTable _Datatable =
                WebConsumer.WVendor_Visit_XMissionStructure.GetServerVisitsByUtilityID(utlityID).Tables[0];
            
            List<EVendor_Visit_XMissionStructure> _StructureList = ParseDatatableVisit(_Datatable);
            return _StructureList;
        }

        #endregion

        #region Mobile

        #region Query

        public static List<EVendor_Visit_XMissionStructure> GetMobile(EXmlOutboxBase database)
        {
            List<EVendor_Visit_XMissionStructure> visits = null;
            if (database.DatabaseType == DatabaseTypes.GPS)
                visits = GetMobileVisits(database.ConnectionString);

            if (database.DatabaseType == DatabaseTypes.Project)
                visits = GetMobileProject(database.ConnectionString);
            return visits;
        }

        public static List<EVendor_Visit_XMissionStructure> GetMobileVisits(string connectionString)
        {
            StringBuilder _QueryMobile = new StringBuilder();
            _QueryMobile.AppendLine("Select ");
            _QueryMobile.AppendLine("   VI.VisitId as Vendor_Visit_XMissionStructureId,");

            _QueryMobile.AppendLine("   VI.ProjectFk as Vendor_ProjectFK,");
            _QueryMobile.AppendLine("   VI.StructureServerID as StructureServerID,");
            _QueryMobile.AppendLine("   VI.AssetFk as UtilityAssetFk,");
            _QueryMobile.AppendLine("   PRO.Name as vendor_Name,");
            
            _QueryMobile.AppendLine("   VI.VisitTypeFk as Vendor_Visit_TypeFK,");
            _QueryMobile.AppendLine("   VType.LookupText as visit_Name,");
            
            _QueryMobile.AppendLine("   VI.TopTypeFk as XMissionStructures_TopTypeFk,");
            _QueryMobile.AppendLine("   VI.TopTypeFk as Pole_TopID,");
            _QueryMobile.AppendLine("   TT.Name as TopType_Name, ");
            _QueryMobile.AppendLine("   TT.Name as Top_Name, ");

            _QueryMobile.AppendLine("   VI.Latitude as Structure_Latitude,");
            _QueryMobile.AppendLine("   VI.Longitude as Structure_Longitude,");
            _QueryMobile.AppendLine("   VI.Altitude as Structure_Altitude,");
            _QueryMobile.AppendLine("   VI.SatelliteCount as Structure_SatelliteCount,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Structure_SatelliteTime,");
            _QueryMobile.AppendLine("   VI.SolutionAvailable as Structure_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Structure_HaveImages,");
            _QueryMobile.AppendLine("   H.SettingValue as HandheldId,");
            _QueryMobile.AppendLine("   H.SettingName as HandheldName,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Date,");

            _QueryMobile.AppendLine("   P.PoleId as Pole_ID,");
            _QueryMobile.AppendLine("   P.Latitude as Pole_Latitude,");
            _QueryMobile.AppendLine("   P.Longitude as Pole_Longitude,");
            _QueryMobile.AppendLine("   P.Altitude as Pole_Altitude,");
            _QueryMobile.AppendLine("   P.SatelliteCount as Pole_SatelliteCount,");
            _QueryMobile.AppendLine("   P.SatelliteTime as Pole_SatelliteTime,");
            _QueryMobile.AppendLine("   P.SolutionAvailable as Pole_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Pole_HaveImages,");
            _QueryMobile.AppendLine("   0 as UploadedImages,");
            _QueryMobile.AppendLine("   0 as TotalImages,");
            _QueryMobile.AppendLine("   P.Year as Pole_Year,");
            _QueryMobile.AppendLine("   P.AID as Pole_AID,");
            _QueryMobile.AppendLine("   P.LVisit as Pole_LVisit,");
            _QueryMobile.AppendLine("   P.Number as Pole_Number,");
            _QueryMobile.AppendLine("   P.Length as Pole_Length,");
            _QueryMobile.AppendLine("   P.PoleServerID as Pole_PoleServerID,");
            _QueryMobile.AppendLine("   P.LVisitor as Pole_LVisitor,");
            
            _QueryMobile.AppendLine("   P.SpeciesFk as Pole_SpeciesID,");
            _QueryMobile.AppendLine("   S.LookupText as Species_Name,");
            
            _QueryMobile.AppendLine("   P.TreatmentFk as Pole_TreatmentID,");
            _QueryMobile.AppendLine("   T.LookupText as Treatment_Name,");
            
            _QueryMobile.AppendLine("   P.ClassFk as Pole_ClassID,");
            _QueryMobile.AppendLine("   C.LookupText as Classes_Name,");

            _QueryMobile.AppendLine("   V.VendorId as Vendor_Id,");
            _QueryMobile.AppendLine("   V.Name as Company_Name,");

            _QueryMobile.AppendLine("   U.UserId as vendor_Contact_Id,");
            _QueryMobile.AppendLine("   U.Name as Vendor_Contact_Name ");
            
            
            _QueryMobile.AppendLine("From ");
            _QueryMobile.AppendLine("   tb_Vendor_Visits AS VI");
            _QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingData AS D ON D.VisitFk = VI.VisitId");
            _QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingPoles AS P ON P.DataFk = D.DataId");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Tops as TT ON VI.TopTypeFk = TT.TopId ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as S ON S.LookupValue = P.SpeciesFk AND S.LookupTypeFk ='Species'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as T ON T.LookupValue = P.TreatmentFk AND T.LookupTypeFk ='Treatments'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as C ON C.LookupValue = P.ClassFk AND C.LookupTypeFk ='Classes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as VType ON VType.LookupValue = VI.VisitTypeFk AND VType.LookupTypeFk ='VisitTypes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Settings as H ON H.SettingValue = VI.Handheld AND H.SettingKey =" + (Int32)EnumMobileSettings.Handheld + "");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Projects as PRO ON PRO.ProjectId = VI.ProjectFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_vendor as V ON V.VendorId = VI.VendorFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Users as U ON U.UserId = VI.UserFk ");
            _QueryMobile.AppendLine(" Order by VI.VisitId");
            
            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_QueryMobile.ToString(), connectionString).Tables[0];

            return ParseListMobileVisits(dt,connectionString);


        }

        public static List<EVendor_Visit_XMissionStructure> GetMobileProject(string connectionString)
        {
            return GetMobileProject(Int32.MinValue, connectionString);
        }

        public static List<EVendor_Visit_XMissionStructure> GetMobileProject(Int32 id,string connectionString)
        {
            StringBuilder _QueryMobile = new StringBuilder();
            _QueryMobile.AppendLine("Select ");
            _QueryMobile.AppendLine("   VI.VisitId as Vendor_Visit_XMissionStructureId,");
            _QueryMobile.AppendLine("   VI.AssetFk as UtilityAssetFk,");
            _QueryMobile.AppendLine("   VI.ProjectFk as Vendor_ProjectFK,");
            _QueryMobile.AppendLine("   VI.VisitId as StructureServerID,");
            _QueryMobile.AppendLine("   PRO.Name as vendor_Name,");

            _QueryMobile.AppendLine("   VI.VisitTypeFk as Vendor_Visit_TypeFK,");
            _QueryMobile.AppendLine("   VType.LookupText as visit_Name,");

            _QueryMobile.AppendLine("   VI.TopTypeFk as XMissionStructures_TopTypeFk,");
            _QueryMobile.AppendLine("   VI.TopTypeFk as Pole_TopID,");
            _QueryMobile.AppendLine("   TT.Name as TopType_Name, ");
            _QueryMobile.AppendLine("   TT.Name as Top_Name, ");

            _QueryMobile.AppendLine("   VI.Latitude as Structure_Latitude,");
            _QueryMobile.AppendLine("   VI.Longitude as Structure_Longitude,");
            _QueryMobile.AppendLine("   VI.Altitude as Structure_Altitude,");
            _QueryMobile.AppendLine("   VI.SatelliteCount as Structure_SatelliteCount,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Structure_SatelliteTime,");
            _QueryMobile.AppendLine("   VI.SolutionAvailable as Structure_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Structure_HaveImages,");
            _QueryMobile.AppendLine("   H.SettingValue as HandheldId,");
            _QueryMobile.AppendLine("   H.SettingName as HandheldName,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Date,");

            _QueryMobile.AppendLine("   P.PoleId as Pole_ID,");
            _QueryMobile.AppendLine("   P.Latitude as Pole_Latitude,");
            _QueryMobile.AppendLine("   P.Longitude as Pole_Longitude,");
            _QueryMobile.AppendLine("   P.Altitude as Pole_Altitude,");
            _QueryMobile.AppendLine("   P.SatelliteCount as Pole_SatelliteCount,");
            _QueryMobile.AppendLine("   P.SatelliteTime as Pole_SatelliteTime,");
            _QueryMobile.AppendLine("   P.SolutionAvailable as Pole_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Pole_HaveImages,");
            _QueryMobile.AppendLine("   0 as UploadedImages,");
            _QueryMobile.AppendLine("   0 as TotalImages,");
            _QueryMobile.AppendLine("   P.Year as Pole_Year,");
            _QueryMobile.AppendLine("   P.AID as Pole_AID,");
            _QueryMobile.AppendLine("   P.LVisit as Pole_LVisit,");
            _QueryMobile.AppendLine("   P.Number as Pole_Number,");
            _QueryMobile.AppendLine("   P.Length as Pole_Length,");
            _QueryMobile.AppendLine("   P.PoleId as Pole_PoleServerID,");
            _QueryMobile.AppendLine("   P.LVisitor as Pole_LVisitor,");

            _QueryMobile.AppendLine("   P.SpeciesFk as Pole_SpeciesID,");
            _QueryMobile.AppendLine("   S.LookupText as Species_Name,");

            _QueryMobile.AppendLine("   P.TreatmentFk as Pole_TreatmentID,");
            _QueryMobile.AppendLine("   T.LookupText as Treatment_Name,");

            _QueryMobile.AppendLine("   P.ClassFk as Pole_ClassID,");
            _QueryMobile.AppendLine("   C.LookupText as Classes_Name,");

            _QueryMobile.AppendLine("   V.VendorId as Vendor_Id,");
            _QueryMobile.AppendLine("   V.Name as Company_Name,");

            _QueryMobile.AppendLine("   U.UserId as vendor_Contact_Id,");
            _QueryMobile.AppendLine("   U.Name as Vendor_Contact_Name ");


            _QueryMobile.AppendLine("From ");
            _QueryMobile.AppendLine("   tb_Vendor_Visits AS VI");
            //_QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingData AS D ON D.VisitFk = VI.VisitId");
            _QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingPoles AS P ON P.VisitFk = VI.VisitId");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Tops as TT ON VI.TopTypeFk = TT.TopId ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as S ON S.LookupValue = P.SpeciesFk AND S.LookupTypeFk ='Species'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as T ON T.LookupValue = P.TreatmentFk AND T.LookupTypeFk ='Treatments'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as C ON C.LookupValue = P.ClassFk AND C.LookupTypeFk ='Classes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as VType ON VType.LookupValue = VI.VisitTypeFk AND VType.LookupTypeFk ='VisitTypes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Settings as H ON H.SettingValue = VI.Handheld AND H.SettingKey =" + (Int32)EnumMobileSettings.Handheld + "");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Projects as PRO ON PRO.ProjectId = VI.ProjectFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_vendor as V ON V.VendorId = VI.VendorFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Users as U ON U.UserId = VI.UserFk ");
            _QueryMobile.AppendLine(" Where (@VisitId is null OR VI.VisitId = @VisitId)");
            _QueryMobile.AppendLine(" Order by VI.VisitId");


            List<SqlParameter> listParameters = new List<SqlParameter>();
            
            if(id == Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@VisitId",ParameterDirection.Input,true,DBNull.Value));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@VisitId", ParameterDirection.Input, false, id));
            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_QueryMobile.ToString(), connectionString,listParameters.ToArray()).Tables[0];

            return ParseListProjectVisits(dt, connectionString);


        }

        public static List<EVendor_Visit_XMissionStructure> GetMobileProjectByAsset(Int32 assetId, string connectionString)
        {
            StringBuilder _QueryMobile = new StringBuilder();
            _QueryMobile.AppendLine("Select ");
            _QueryMobile.AppendLine("   VI.VisitId as Vendor_Visit_XMissionStructureId,");
            _QueryMobile.AppendLine("   VI.AssetFk as UtilityAssetFk,");
            _QueryMobile.AppendLine("   VI.ProjectFk as Vendor_ProjectFK,");
            _QueryMobile.AppendLine("   VI.VisitId as StructureServerID,");
            _QueryMobile.AppendLine("   PRO.Name as vendor_Name,");

            _QueryMobile.AppendLine("   VI.VisitTypeFk as Vendor_Visit_TypeFK,");
            _QueryMobile.AppendLine("   VType.LookupText as visit_Name,");

            _QueryMobile.AppendLine("   VI.TopTypeFk as XMissionStructures_TopTypeFk,");
            _QueryMobile.AppendLine("   VI.TopTypeFk as Pole_TopID,");
            _QueryMobile.AppendLine("   TT.Name as TopType_Name, ");
            _QueryMobile.AppendLine("   TT.Name as Top_Name, ");

            _QueryMobile.AppendLine("   VI.Latitude as Structure_Latitude,");
            _QueryMobile.AppendLine("   VI.Longitude as Structure_Longitude,");
            _QueryMobile.AppendLine("   VI.Altitude as Structure_Altitude,");
            _QueryMobile.AppendLine("   VI.SatelliteCount as Structure_SatelliteCount,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Structure_SatelliteTime,");
            _QueryMobile.AppendLine("   VI.SolutionAvailable as Structure_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Structure_HaveImages,");
            _QueryMobile.AppendLine("   H.SettingValue as HandheldId,");
            _QueryMobile.AppendLine("   H.SettingName as HandheldName,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Date,");

            _QueryMobile.AppendLine("   P.PoleId as Pole_ID,");
            _QueryMobile.AppendLine("   P.Latitude as Pole_Latitude,");
            _QueryMobile.AppendLine("   P.Longitude as Pole_Longitude,");
            _QueryMobile.AppendLine("   P.Altitude as Pole_Altitude,");
            _QueryMobile.AppendLine("   P.SatelliteCount as Pole_SatelliteCount,");
            _QueryMobile.AppendLine("   P.SatelliteTime as Pole_SatelliteTime,");
            _QueryMobile.AppendLine("   P.SolutionAvailable as Pole_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Pole_HaveImages,");
            _QueryMobile.AppendLine("   0 as UploadedImages,");
            _QueryMobile.AppendLine("   0 as TotalImages,");
            _QueryMobile.AppendLine("   P.Year as Pole_Year,");
            _QueryMobile.AppendLine("   P.AID as Pole_AID,");
            _QueryMobile.AppendLine("   P.LVisit as Pole_LVisit,");
            _QueryMobile.AppendLine("   P.Number as Pole_Number,");
            _QueryMobile.AppendLine("   P.Length as Pole_Length,");
            _QueryMobile.AppendLine("   P.PoleId as Pole_PoleServerID,");
            _QueryMobile.AppendLine("   P.LVisitor as Pole_LVisitor,");

            _QueryMobile.AppendLine("   P.SpeciesFk as Pole_SpeciesID,");
            _QueryMobile.AppendLine("   S.LookupText as Species_Name,");

            _QueryMobile.AppendLine("   P.TreatmentFk as Pole_TreatmentID,");
            _QueryMobile.AppendLine("   T.LookupText as Treatment_Name,");

            _QueryMobile.AppendLine("   P.ClassFk as Pole_ClassID,");
            _QueryMobile.AppendLine("   C.LookupText as Classes_Name,");

            _QueryMobile.AppendLine("   V.VendorId as Vendor_Id,");
            _QueryMobile.AppendLine("   V.Name as Company_Name,");

            _QueryMobile.AppendLine("   U.UserId as vendor_Contact_Id,");
            _QueryMobile.AppendLine("   U.Name as Vendor_Contact_Name ");


            _QueryMobile.AppendLine("From ");
            _QueryMobile.AppendLine("   tb_Vendor_Visits AS VI");
            //_QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingData AS D ON D.VisitFk = VI.VisitId");
            _QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingPoles AS P ON P.VisitFk = VI.VisitId");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Tops as TT ON VI.TopTypeFk = TT.TopId ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as S ON S.LookupValue = P.SpeciesFk AND S.LookupTypeFk ='Species'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as T ON T.LookupValue = P.TreatmentFk AND T.LookupTypeFk ='Treatments'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as C ON C.LookupValue = P.ClassFk AND C.LookupTypeFk ='Classes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as VType ON VType.LookupValue = VI.VisitTypeFk AND VType.LookupTypeFk ='VisitTypes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Settings as H ON H.SettingValue = VI.Handheld AND H.SettingKey =" + (Int32)EnumMobileSettings.Handheld + "");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Projects as PRO ON PRO.ProjectId = VI.ProjectFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_vendor as V ON V.VendorId = VI.VendorFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Users as U ON U.UserId = VI.UserFk ");
            _QueryMobile.AppendLine(" Where (VI.AssetFk is null OR VI.AssetFk = @AssetId)");
            _QueryMobile.AppendLine(" Order by VI.VisitId");


            List<SqlParameter> listParameters = new List<SqlParameter>();

            if(assetId == Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@AssetId", ParameterDirection.Input, true, DBNull.Value));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@AssetId", ParameterDirection.Input, false, assetId));
            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_QueryMobile.ToString(), connectionString, listParameters.ToArray()).Tables[0];

            return ParseListProjectVisits(dt, connectionString);


        }

        public static Int32 CountMobileVisit(string connectionString)
        {
            StringBuilder _QueryMobile = new StringBuilder();
            _QueryMobile.AppendLine("select Count(*) from  tb_Vendor_Visits");
            return (Int32)LMobileDatabase.ExecuteScalar(_QueryMobile.ToString(),connectionString);


        }

        private static List<EVendor_Visit_XMissionStructure> ParseListMobileVisits(DataTable _Datatable, string connectionString)
        {
            Int32 StructureIdFlag = Int32.MinValue;
            Int32 _CurrentStructureID;

            DatabaseTypes _DatabaseType = LOutbox.GetDatabaseType(connectionString);

            EVendor_Visit_XMissionStructure _Structure;
            List<EVendor_Visit_XMissionStructure> _StructureList = new List<EVendor_Visit_XMissionStructure>();
            EVendor_Visit_XMissionStructures_Pole _Pole;

            List<EVendor_Visit_XMissionStructures_Image> _ImageList =
                LVendor_Visit_XMissionStructures_Image.GetAll(connectionString);
            List<EVendor_Visit_XMissionStructures_Pole> _PoleList = new List<EVendor_Visit_XMissionStructures_Pole>();

            DataRow _DataRow = null;
            DataRow _PreviusRow = null;
            for (int i = 0; i < _Datatable.Rows.Count; i++)
            {
                _DataRow = _Datatable.Rows[i];
                if (i != 0)
                    _PreviusRow = _Datatable.Rows[i - 1];

                _CurrentStructureID = ConvertHelper.ToInt32(_DataRow["Vendor_Visit_XMissionStructureId"]);


                if (StructureIdFlag != Int32.MinValue)
                {
                    //Do control break
                    if (StructureIdFlag != _CurrentStructureID)
                    {
                        _Structure = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]));
                        _Structure.UtilityAsset = new EUtility_Asset_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["UtilityAssetFk"])); 
                        _Structure.Altitude = ConvertHelper.Todouble(_PreviusRow["Structure_Altitude"]);
                        _Structure.DatabaseType = _DatabaseType;
                        _Structure.Longitude = ConvertHelper.Todouble(_PreviusRow["Structure_Longitude"]);
                        _Structure.Latitude = ConvertHelper.Todouble(_PreviusRow["Structure_Latitude"]);
                        _Structure.SatelliteCount = ConvertHelper.Toint(_PreviusRow["Structure_SatelliteCount"]);
                        _Structure.SatelliteTime = ConvertHelper.ToDateTime(_PreviusRow["Structure_SatelliteTime"]);
                        _Structure.SolutionAvailable = ConvertHelper.Tobool(_PreviusRow["Structure_SolutionAvailable"]);
                        _Structure.Date = ConvertHelper.ToDateTime(_PreviusRow["Date"]);
                        _Structure.ServerID = ConvertHelper.ToInt32(_PreviusRow["StructureServerID"]);
                        _Structure.Vendor_Visit_Type.Name = ConvertHelper.Tostring(_PreviusRow["visit_Name"]);
                        _Structure.Vendor_Visit_Type.Vendor_Visit_TypeId = ConvertHelper.Toint(_PreviusRow["Vendor_Visit_TypeFK"]);
                        foreach (EBasePole p in _PoleList)
                        {
                            if (!_Structure.Poles.Contains(p))
                            {
                                p.Asset = _Structure;
                                _Structure.Poles.Add(p);
                            }

                        }
                        _Structure.Vendor_Project = new EVendor_Project(ConvertHelper.Toint(_PreviusRow["Vendor_ProjectFK"]), ConvertHelper.Tostring(_PreviusRow["vendor_Name"]));
                        _Structure.Vendor_Project.Vendor = new EVendor(ConvertHelper.Toint(_PreviusRow["Vendor_Id"]), ConvertHelper.Tostring(_PreviusRow["Company_Name"]), "");
                        _Structure.Vendor_Contact = new EVendor_Contact(ConvertHelper.ToInt32(_PreviusRow["vendor_Contact_Id"]));
                        _Structure.Vendor_Contact.Name = ConvertHelper.Tostring(_PreviusRow["Vendor_Contact_Name"]);
                        _Structure.Handheld = new EHandheld(ConvertHelper.ToInt32(_PreviusRow["HandheldId"]), _PreviusRow["HandheldName"].ToString());
                        LVendor_Visit_XMissionStructures_Image.AssignToAsset(_Structure, _ImageList);
                        _Structure.TopType = new EXMissionStructures_TopType(ConvertHelper.Toint(_PreviusRow["XMissionStructures_TopTypeFk"]), ConvertHelper.Tostring(_PreviusRow["TopType_Name"]));
                        _StructureList.Add(_Structure);
                        StructureIdFlag = _CurrentStructureID;
                        _PoleList = new List<EVendor_Visit_XMissionStructures_Pole>();
                    }
                }

                // Fill Poles Entities
                if (ConvertHelper.ToInt32(_DataRow["Pole_ID"]) != Int32.MinValue)
                {
                    _Pole = new EVendor_Visit_XMissionStructures_Pole();
                    _Pole.Altitude = ConvertHelper.Todouble(_DataRow["Pole_Altitude"]);
                    _Pole.Latitude = ConvertHelper.Todouble(_DataRow["Pole_Latitude"]);
                    _Pole.Longitude = ConvertHelper.Todouble(_DataRow["Pole_Longitude"]);
                    _Pole.Id = ConvertHelper.ToInt32(_DataRow["Pole_ID"]);
                    _Pole.ServerID = ConvertHelper.ToInt32(_DataRow["Pole_PoleServerID"]);
                    _Pole.SatelliteTime = ConvertHelper.ToDateTime(_DataRow["Pole_SatelliteTime"]);
                    _Pole.SatelliteCount = ConvertHelper.Toint(_DataRow["Pole_SatelliteCount"]);
                    _Pole.SolutionAvailable = ConvertHelper.Tobool(_DataRow["Pole_SolutionAvailable"]);
                    _Pole.Asset = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_DataRow["Vendor_Visit_XMissionStructureId"]));
                    LVendor_Visit_XMissionStructures_Image.AssignToPole(_Pole, _ImageList);
                    _Pole.Year = ConvertHelper.Toint(_DataRow["Pole_Year"]);
                    _Pole.AID = ConvertHelper.Tostring(_DataRow["Pole_AID"]);
                    _Pole.LVisit = ConvertHelper.Toint(_DataRow["Pole_LVisit"]);
                    _Pole.Number = ConvertHelper.Tostring(_DataRow["Pole_Number"]);
                    _Pole.Length = ConvertHelper.Todouble(_DataRow["Pole_Length"]);
                    _Pole.LVisitor = ConvertHelper.Tostring(_DataRow["Pole_LVisitor"]);

                    _Pole.Class = new EXMissionStructures_PoleClass(ConvertHelper.Toint(_DataRow["Pole_ClassID"]), ConvertHelper.Tostring(_DataRow["Classes_Name"]));
                    _Pole.TopType = new EXMissionStructures_PoleTopType(ConvertHelper.Toint(_DataRow["Pole_TopID"]), ConvertHelper.Tostring(_DataRow["Top_Name"]));
                    _Pole.Species = new EXMissionStructures_PoleSpecies(ConvertHelper.Toint(_DataRow["Pole_SpeciesID"]), ConvertHelper.Tostring(_DataRow["Species_Name"]));
                    _Pole.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.Toint(_DataRow["Pole_TreatmentID"]), ConvertHelper.Tostring(_DataRow["Treatment_Name"]));

                    _PoleList.Add(_Pole);
                }

                if (StructureIdFlag == Int32.MinValue)
                    StructureIdFlag = _CurrentStructureID;


            }
            if (_Datatable.Rows.Count > 0)
            {
                _PreviusRow = _Datatable.Rows[_Datatable.Rows.Count - 1];
                //Add last structure
                _Structure = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]));
                _Structure.UtilityAsset = new EUtility_Asset_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["UtilityAssetFk"])); 
                _Structure.Altitude = ConvertHelper.Todouble(_PreviusRow["Structure_Altitude"]);
                _Structure.Longitude = ConvertHelper.Todouble(_PreviusRow["Structure_Longitude"]);
                _Structure.Latitude = ConvertHelper.Todouble(_PreviusRow["Structure_Latitude"]);
                _Structure.SatelliteCount = ConvertHelper.Toint(_PreviusRow["Structure_SatelliteCount"]);
                _Structure.SatelliteTime = ConvertHelper.ToDateTime(_PreviusRow["Structure_SatelliteTime"]);
                _Structure.SolutionAvailable = ConvertHelper.Tobool(_PreviusRow["Structure_SolutionAvailable"]);
                _Structure.Date = ConvertHelper.ToDateTime(_PreviusRow["Date"]);
                _Structure.Vendor_Project = new EVendor_Project(ConvertHelper.Toint(_PreviusRow["Vendor_ProjectFK"]), ConvertHelper.Tostring(_PreviusRow["vendor_Name"]));
                _Structure.Vendor_Project.Vendor = new EVendor(ConvertHelper.Toint(_PreviusRow["Vendor_Id"]), ConvertHelper.Tostring(_PreviusRow["Company_Name"]), "");
                _Structure.Vendor_Contact = new EVendor_Contact(ConvertHelper.ToInt32(_PreviusRow["vendor_Contact_Id"]));
                _Structure.Vendor_Contact.Name = ConvertHelper.Tostring(_PreviusRow["Vendor_Contact_Name"]);
                _Structure.Vendor_Visit_Type.Name = ConvertHelper.Tostring(_PreviusRow["visit_Name"]);
                _Structure.Vendor_Visit_Type.Vendor_Visit_TypeId = ConvertHelper.Toint(_PreviusRow["Vendor_Visit_TypeFK"]);
                _Structure.ServerID = ConvertHelper.ToInt32(_PreviusRow["StructureServerID"]);
                _Structure.Handheld = new EHandheld(ConvertHelper.ToInt32(_PreviusRow["HandheldId"]), _PreviusRow["HandheldName"].ToString());
                _Structure.DatabaseType = _DatabaseType;
                _Structure.TopType = new EXMissionStructures_TopType(ConvertHelper.Toint(_PreviusRow["XMissionStructures_TopTypeFk"]), ConvertHelper.Tostring(_PreviusRow["TopType_Name"]));
                LVendor_Visit_XMissionStructures_Image.AssignToAsset(_Structure, _ImageList);
                foreach (EBasePole p in _PoleList)
                {
                    if (!_Structure.Poles.Contains(p))
                    {
                        p.Asset = _Structure;
                        _Structure.Poles.Add(p);
                    }
                }
                _StructureList.Add(_Structure);
            }
            return _StructureList;
        }

        private static List<EVendor_Visit_XMissionStructure> ParseListProjectVisits(DataTable _Datatable, string connectionString)
        {
            Int32 StructureIdFlag = Int32.MinValue;
            Int32 _CurrentStructureID;

            DatabaseTypes _DatabaseType = LOutbox.GetDatabaseType(connectionString);

            EVendor_Visit_XMissionStructure _Structure;
            List<EVendor_Visit_XMissionStructure> _StructureList = new List<EVendor_Visit_XMissionStructure>();
            EVendor_Visit_XMissionStructures_Pole _Pole;

            List<EVendor_Visit_XMissionStructures_Image> _ImageList =
                LVendor_Visit_XMissionStructures_Image.GetAll(connectionString);
            List<EVendor_Visit_XMissionStructures_Pole> _PoleList = new List<EVendor_Visit_XMissionStructures_Pole>();

            DataRow _DataRow = null;
            DataRow _PreviusRow = null;
            for (int i = 0; i < _Datatable.Rows.Count; i++)
            {
                _DataRow = _Datatable.Rows[i];
                if (i != 0)
                    _PreviusRow = _Datatable.Rows[i - 1];

                _CurrentStructureID = ConvertHelper.ToInt32(_DataRow["Vendor_Visit_XMissionStructureId"]);


                if (StructureIdFlag != Int32.MinValue)
                {
                    //Do control break
                    if (StructureIdFlag != _CurrentStructureID)
                    {
                        _Structure = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]));
                        _Structure.UtilityAsset = new EUtility_Asset_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["UtilityAssetFk"]));
                        _Structure.Altitude = ConvertHelper.Todouble(_PreviusRow["Structure_Altitude"]);
                        _Structure.DatabaseType = _DatabaseType;
                        _Structure.Longitude = ConvertHelper.Todouble(_PreviusRow["Structure_Longitude"]);
                        _Structure.Latitude = ConvertHelper.Todouble(_PreviusRow["Structure_Latitude"]);
                        _Structure.SatelliteCount = ConvertHelper.Toint(_PreviusRow["Structure_SatelliteCount"]);
                        _Structure.SatelliteTime = ConvertHelper.ToDateTime(_PreviusRow["Structure_SatelliteTime"]);
                        _Structure.SolutionAvailable = ConvertHelper.Tobool(_PreviusRow["Structure_SolutionAvailable"]);
                        _Structure.Date = ConvertHelper.ToDateTime(_PreviusRow["Date"]);
                        _Structure.ServerID = ConvertHelper.ToInt32(_PreviusRow["StructureServerID"]);
                        _Structure.Vendor_Visit_Type.Name = ConvertHelper.Tostring(_PreviusRow["visit_Name"]);
                        _Structure.Vendor_Visit_Type.Vendor_Visit_TypeId = ConvertHelper.Toint(_PreviusRow["Vendor_Visit_TypeFK"]);
                        foreach (EBasePole p in _PoleList)
                        {
                            if (!_Structure.Poles.Contains(p))
                            {
                                p.Asset = _Structure;
                                _Structure.Poles.Add(p);
                            }

                        }
                        _Structure.Vendor_Project = new EVendor_Project(ConvertHelper.Toint(_PreviusRow["Vendor_ProjectFK"]), ConvertHelper.Tostring(_PreviusRow["vendor_Name"]));
                        _Structure.Vendor_Project.Vendor = new EVendor(ConvertHelper.Toint(_PreviusRow["Vendor_Id"]), ConvertHelper.Tostring(_PreviusRow["Company_Name"]), "");
                        _Structure.Vendor_Contact = new EVendor_Contact(ConvertHelper.ToInt32(_PreviusRow["vendor_Contact_Id"]));
                        _Structure.Vendor_Contact.Name = ConvertHelper.Tostring(_PreviusRow["Vendor_Contact_Name"]);
                        _Structure.Handheld = new EHandheld(ConvertHelper.ToInt32(_PreviusRow["HandheldId"]), _PreviusRow["HandheldName"].ToString());
                        LVendor_Visit_XMissionStructures_Image.AssignToAsset(_Structure, _ImageList);
                        _Structure.TopType = new EXMissionStructures_TopType(ConvertHelper.Toint(_PreviusRow["XMissionStructures_TopTypeFk"]), ConvertHelper.Tostring(_PreviusRow["TopType_Name"]));
                        _StructureList.Add(_Structure);
                        StructureIdFlag = _CurrentStructureID;
                        _PoleList = new List<EVendor_Visit_XMissionStructures_Pole>();
                    }
                }

                // Fill Poles Entities
                if (ConvertHelper.ToInt32(_DataRow["Pole_ID"]) != Int32.MinValue)
                {
                    _Pole = new EVendor_Visit_XMissionStructures_Pole();
                    _Pole.Altitude = ConvertHelper.Todouble(_DataRow["Pole_Altitude"]);
                    _Pole.Latitude = ConvertHelper.Todouble(_DataRow["Pole_Latitude"]);
                    _Pole.Longitude = ConvertHelper.Todouble(_DataRow["Pole_Longitude"]);
                    _Pole.Id = ConvertHelper.ToInt32(_DataRow["Pole_ID"]);
                    _Pole.ServerID = ConvertHelper.ToInt32(_DataRow["Pole_PoleServerID"]);
                    _Pole.SatelliteTime = ConvertHelper.ToDateTime(_DataRow["Pole_SatelliteTime"]);
                    _Pole.SatelliteCount = ConvertHelper.Toint(_DataRow["Pole_SatelliteCount"]);
                    _Pole.SolutionAvailable = ConvertHelper.Tobool(_DataRow["Pole_SolutionAvailable"]);
                    _Pole.Asset = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_DataRow["Vendor_Visit_XMissionStructureId"]));
                    LVendor_Visit_XMissionStructures_Image.AssignToPole(_Pole, _ImageList);
                    _Pole.Year = ConvertHelper.Toint(_DataRow["Pole_Year"]);
                    _Pole.AID = ConvertHelper.Tostring(_DataRow["Pole_AID"]);
                    _Pole.LVisit = ConvertHelper.Toint(_DataRow["Pole_LVisit"]);
                    _Pole.Number = ConvertHelper.Tostring(_DataRow["Pole_Number"]);
                    _Pole.Length = ConvertHelper.Todouble(_DataRow["Pole_Length"]);
                    _Pole.LVisitor = ConvertHelper.Tostring(_DataRow["Pole_LVisitor"]);

                    _Pole.Class = new EXMissionStructures_PoleClass(ConvertHelper.Toint(_DataRow["Pole_ClassID"]), ConvertHelper.Tostring(_DataRow["Classes_Name"]));
                    _Pole.TopType = new EXMissionStructures_PoleTopType(ConvertHelper.Toint(_DataRow["Pole_TopID"]), ConvertHelper.Tostring(_DataRow["Top_Name"]));
                    _Pole.Species = new EXMissionStructures_PoleSpecies(ConvertHelper.Toint(_DataRow["Pole_SpeciesID"]), ConvertHelper.Tostring(_DataRow["Species_Name"]));
                    _Pole.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.Toint(_DataRow["Pole_TreatmentID"]), ConvertHelper.Tostring(_DataRow["Treatment_Name"]));

                    _PoleList.Add(_Pole);
                }

                if (StructureIdFlag == Int32.MinValue)
                    StructureIdFlag = _CurrentStructureID;


            }
            if (_Datatable.Rows.Count > 0)
            {
                _PreviusRow = _Datatable.Rows[_Datatable.Rows.Count - 1];
                //Add last structure
                _Structure = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["Vendor_Visit_XMissionStructureId"]));
                _Structure.UtilityAsset = new EUtility_Asset_XMissionStructure(ConvertHelper.ToInt32(_PreviusRow["UtilityAssetFk"]));
                _Structure.Altitude = ConvertHelper.Todouble(_PreviusRow["Structure_Altitude"]);
                _Structure.Longitude = ConvertHelper.Todouble(_PreviusRow["Structure_Longitude"]);
                _Structure.Latitude = ConvertHelper.Todouble(_PreviusRow["Structure_Latitude"]);
                _Structure.SatelliteCount = ConvertHelper.Toint(_PreviusRow["Structure_SatelliteCount"]);
                _Structure.SatelliteTime = ConvertHelper.ToDateTime(_PreviusRow["Structure_SatelliteTime"]);
                _Structure.SolutionAvailable = ConvertHelper.Tobool(_PreviusRow["Structure_SolutionAvailable"]);
                _Structure.Date = ConvertHelper.ToDateTime(_PreviusRow["Date"]);
                _Structure.Vendor_Project = new EVendor_Project(ConvertHelper.Toint(_PreviusRow["Vendor_ProjectFK"]), ConvertHelper.Tostring(_PreviusRow["vendor_Name"]));
                _Structure.Vendor_Project.Vendor = new EVendor(ConvertHelper.Toint(_PreviusRow["Vendor_Id"]), ConvertHelper.Tostring(_PreviusRow["Company_Name"]), "");
                _Structure.Vendor_Contact = new EVendor_Contact(ConvertHelper.ToInt32(_PreviusRow["vendor_Contact_Id"]));
                _Structure.Vendor_Contact.Name = ConvertHelper.Tostring(_PreviusRow["Vendor_Contact_Name"]);
                _Structure.Vendor_Visit_Type.Name = ConvertHelper.Tostring(_PreviusRow["visit_Name"]);
                _Structure.Vendor_Visit_Type.Vendor_Visit_TypeId = ConvertHelper.Toint(_PreviusRow["Vendor_Visit_TypeFK"]);
                _Structure.ServerID = ConvertHelper.ToInt32(_PreviusRow["StructureServerID"]);
                _Structure.Handheld = new EHandheld(ConvertHelper.ToInt32(_PreviusRow["HandheldId"]), _PreviusRow["HandheldName"].ToString());
                _Structure.DatabaseType = _DatabaseType;
                _Structure.TopType = new EXMissionStructures_TopType(ConvertHelper.Toint(_PreviusRow["XMissionStructures_TopTypeFk"]), ConvertHelper.Tostring(_PreviusRow["TopType_Name"]));
                LVendor_Visit_XMissionStructures_Image.AssignToAsset(_Structure, _ImageList);
                foreach (EBasePole p in _PoleList)
                {
                    if (!_Structure.Poles.Contains(p))
                    {
                        p.Asset = _Structure;
                        _Structure.Poles.Add(p);
                    }
                }
                _StructureList.Add(_Structure);
            }
            return _StructureList;
        }

        public static bool CheckIsValidVisit(EVendor_Visit_XMissionStructure visit, ref string errorMessage)
        {
            StringBuilder _tmpErrorMessage = new StringBuilder();
            bool _foundedErrors = false;
            if(visit.Latitude == double.MinValue || visit.Longitude == double.MinValue)
            {
                _tmpErrorMessage.AppendLine("Latitude or Longitude is incomplete in Visit " + visit.Id);
                _foundedErrors = true;
            }
            foreach (EVendor_Visit_XMissionStructures_Pole p in visit.Poles)
            {
                if (p.Latitude == double.MinValue || p.Longitude == double.MinValue)
                {
                    _tmpErrorMessage.AppendLine("Latitude or Longitude is incomplete in Pole " + p.Id);
                    _foundedErrors = true;
                }
            }
            errorMessage = _tmpErrorMessage.ToString();
            return _foundedErrors;
        }

        #endregion

        #region CRUD

        public static void UpdateUploadedData(EVendor_Visit_XMissionStructure visit, string connectionString)
        {
            StringBuilder _query = new StringBuilder();



            _query.AppendLine("Update tb_Vendor_Visits set StructureServerID =" + visit.ServerID + ",UploadedDate = " +
                              DateTime.Now.ToString("yyyy-MM-dd") + " where VisitId =" + visit.Id + " ");

            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
            _query = new StringBuilder();
            _query.AppendLine("update tb_Vendor_Visit_Images set StructureServerID =" + visit.ServerID +
                              " where VisitFk =" + visit.Id + "   ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
        }

        public static Int32 SaveMobile(EVendor_Visit_XMissionStructure visit, string connectionString)
        {

            try
            {
                UpdateMobile(visit, connectionString);
            }
            catch (Exception ex)
            {
                
                throw;
            }
            return visit.Id;

        }

        private static void UpdateMobile(EVendor_Visit_XMissionStructure visit, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();

            #region Query

            _Query.AppendLine("update tb_Vendor_Visits ");
            _Query.AppendLine("     SET Latitude = @Latitude, ");
            _Query.AppendLine("     Longitude = @Longitude, ");
            _Query.AppendLine("     Altitude = @Altitude, ");
            _Query.AppendLine("     SatelliteCount = @SatelliteCount, ");
            _Query.AppendLine("     SatelliteTime = @SatelliteTime, ");
            _Query.AppendLine("     ProjectFk = @ProjectFK, ");
            _Query.AppendLine("     TopTypeFk = @TopTypeFk, ");
            _Query.AppendLine("     VisitTypeFk = @VisitTypeFk, ");
            _Query.AppendLine("     VendorFk = @VendorFK, ");
            _Query.AppendLine("     UserFk = @UserFK, ");
            _Query.AppendLine("     Handheld = @HandheldFk ");
            _Query.AppendLine("Where VisitId = @VisitIdFk ");


            #endregion

            #region Parameters

            List<SqlParameter> listParameter = new List<SqlParameter>();
            
            SqlParameter tmpParameter = null;

            if (visit.Latitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, visit.Latitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if(visit.Longitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, visit.Longitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Altitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, visit.Altitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (visit.SatelliteCount > 0)
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, visit.SatelliteCount);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (visit.SatelliteTime != DateTime.MinValue)
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, false, visit.SatelliteTime);
            else
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Vendor_Project.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ProjectFK", ParameterDirection.Input, false, visit.Vendor_Project.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ProjectFK", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.TopType.XMissionStructures_TopTypeId != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, visit.TopType.XMissionStructures_TopTypeId);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Vendor_Visit_Type.Vendor_Visit_TypeId != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, visit.Vendor_Visit_Type.Vendor_Visit_TypeId);
            else
                tmpParameter = DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Vendor_Project.Vendor.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@VendorFK", ParameterDirection.Input, false, visit.Vendor_Project.Vendor.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@VendorFK", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Vendor_Contact.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@UserFK", ParameterDirection.Input, false, visit.Vendor_Contact.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@UserFK", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Handheld.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@HandheldFk", ParameterDirection.Input, false, visit.Handheld.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@HandheldFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            tmpParameter = DBHelper.CreateIntParameter("@VisitIdFk", ParameterDirection.Input, false, visit.Id);
            listParameter.Add(tmpParameter);

            #endregion

            try
            {
                LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());

                foreach (EVendor_Visit_XMissionStructures_Pole p in visit.Poles)
                {
                    LVendor_Visit_XMissionStructures_Pole.SaveMobile(p,connectionString);
                }

                foreach (EVendor_Visit_XMissionStructures_Image i in visit.Images)
                {
                    LVendor_Visit_XMissionStructures_Image.SaveMobile(i, connectionString);

                }
            }
            catch(Exception e)
            {
            }
        }

        public static void DeleteMobile(EVendor_Visit_XMissionStructure visit, string connectionString)
        {

            try
            {
                List<SqlParameter> listParameter = new List<SqlParameter>();
                listParameter.Add(DBHelper.CreateIntParameter("@VisitId",ParameterDirection.Input,false,visit.Id));


                foreach (EVendor_Visit_XMissionStructures_Image i in visit.Images)
                {
                    LVendor_Visit_XMissionStructures_Image.DeleteMobile(i,connectionString);
                }
                foreach (EVendor_Visit_XMissionStructures_Pole p in visit.Poles)
                {
                    LVendor_Visit_XMissionStructures_Pole.DeleteMobile(p,connectionString);
                }


                StringBuilder _Query= new StringBuilder();
                #region Query

                _Query.AppendLine("Delete tb_Vendor_Visit_XStructureMappingData where VisitFk = @VisitId");

                #endregion

                LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());
                
                _Query = new StringBuilder();
                #region Query

                _Query.AppendLine("Delete tb_Vendor_Visits where VisitId = @VisitId");

                #endregion

                LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameter.ToArray());



            }
            catch (Exception ex)
            {

                throw;
            }

        }

        public static void DeleteProject(EVendor_Visit_XMissionStructure visit, string connectionString)
        {

            try
            {
                List<SqlParameter> listParameter = new List<SqlParameter>();
                listParameter.Add(DBHelper.CreateIntParameter("@VisitId", ParameterDirection.Input, false, visit.Id));


                foreach (EVendor_Visit_XMissionStructures_Image i in visit.Images)
                {
                    LVendor_Visit_XMissionStructures_Image.DeleteMobile(i, connectionString);
                }
                foreach (EVendor_Visit_XMissionStructures_Pole p in visit.Poles)
                {
                    LVendor_Visit_XMissionStructures_Pole.DeleteMobile(p, connectionString);
                }


                StringBuilder _Query = new StringBuilder();
                #region Query

                _Query.AppendLine("Delete tb_Vendor_Visits where VisitId = @VisitId");

                #endregion

                LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());



            }
            catch (Exception ex)
            {

                throw;
            }

        }

        public static void InsertMobileProject(EVendor_Visit_XMissionStructure visit,bool isShowImages, string connectionString)
        {

            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameter = new List<SqlParameter>();

            #region Query
            _Query.AppendLine(" Insert into tb_Vendor_Visits ");
            _Query.AppendLine(" (");
            _Query.AppendLine("     VisitId,");
            _Query.AppendLine("     VisitTypeFk,");
            _Query.AppendLine("     Latitude,");
            _Query.AppendLine("     Longitude,");
            _Query.AppendLine("     Altitude,");
            _Query.AppendLine("     SatelliteCount,");
            _Query.AppendLine("     SatelliteTime,");
            _Query.AppendLine("     SolutionAvailable,");
            _Query.AppendLine("     ProjectFk,");
            _Query.AppendLine("     VendorFk,");
            _Query.AppendLine("     UserFk,");
            _Query.AppendLine("     Handheld,");
            _Query.AppendLine("     TopTypeFk,");
            _Query.AppendLine("     StructureServerID,");
            _Query.AppendLine("     UploadedDate");
            _Query.AppendLine(" )Values(");

            _Query.AppendLine("     @VisitId,");
            _Query.AppendLine("     @VisitTypeFk,");
            _Query.AppendLine("     @Latitude,");
            _Query.AppendLine("     @Longitude,");
            _Query.AppendLine("     @Altitude,");
            _Query.AppendLine("     @SatelliteCount,");
            _Query.AppendLine("     @SatelliteTime,");
            _Query.AppendLine("     @SolutionAvailable,");
            _Query.AppendLine("     @ProjectFk,");
            _Query.AppendLine("     @VendorFk,");
            _Query.AppendLine("     @UserFk,");
            _Query.AppendLine("     @Handheld,");
            _Query.AppendLine("     @TopTypeFk,");
            _Query.AppendLine("     @StructureServerID,");
            _Query.AppendLine("     @UploadedDate");
            _Query.AppendLine(" )");
            #endregion

            #region Parameters


            SqlParameter tmpParameter = null;
            listParameter.Add(DBHelper.CreateIntParameter("@VisitId", ParameterDirection.Input, false, visit.Id));
            listParameter.Add(DBHelper.CreateIntParameter("@StructureServerID", ParameterDirection.Input, false, visit.Id));
            listParameter.Add(DBHelper.CreateDateTimeParameter("@UploadedDate", ParameterDirection.Input, false, DateTime.Now));
            listParameter.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, visit.SolutionAvailable));

            if (visit.Latitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, visit.Latitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Longitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, visit.Longitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Altitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, visit.Altitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (visit.SatelliteCount > 0)
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, visit.SatelliteCount);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (visit.SatelliteTime != DateTime.MinValue)
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, false, visit.SatelliteTime);
            else
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);



            if (visit.Vendor_Project.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ProjectFK", ParameterDirection.Input, false, visit.Vendor_Project.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ProjectFK", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.TopType.XMissionStructures_TopTypeId != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, visit.TopType.XMissionStructures_TopTypeId);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Vendor_Visit_Type.Vendor_Visit_TypeId != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, visit.Vendor_Visit_Type.Vendor_Visit_TypeId);
            else
                tmpParameter = DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Vendor_Project.Vendor.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@VendorFK", ParameterDirection.Input, false, visit.Vendor_Project.Vendor.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@VendorFK", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Vendor_Contact.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@UserFK", ParameterDirection.Input, false, visit.Vendor_Contact.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@UserFK", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (visit.Handheld.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@Handheld", ParameterDirection.Input, false, visit.Handheld.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@Handheld", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);
            

            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameter.ToArray());

            foreach (EVendor_Visit_XMissionStructures_Pole p in visit.Poles)
            {
                LVendor_Visit_XMissionStructures_Pole.InsertMobileProject(p, connectionString);
            }

            if(isShowImages)
            {
                foreach (EVendor_Visit_XMissionStructures_Image i in visit.Images)
                    LVendor_Visit_XMissionStructures_Image.InsertMobileProject(i, connectionString);
            }


        }

        #endregion

        

        #endregion

        #region CRUD

        public static Int32 Save(EVendor_Visit_XMissionStructure visit)
        {
            return
                WebConsumer.WVendor_Visit_XMissionStructure.Save(visit.Id, visit.Vendor_Project.Id, Int32.MinValue,
                                                                 visit.Vendor_Visit_Type.Vendor_Visit_TypeId,
                                                                 visit.TopType.XMissionStructures_TopTypeId,
                                                                 visit.Latitude, visit.Longitude, visit.Altitude,
                                                                 visit.SatelliteCount, visit.SatelliteTime,
                                                                 visit.SolutionAvailable, visit.Handheld.Id, visit.Vendor_Contact.Id,
                                                                 visit.Date, visit.TotalImages);

        }

        public static void Delete(EVendor_Visit_XMissionStructure visit)
        {
            WebConsumer.WVendor_Visit_XMissionStructure.Delete(visit.Id);
        }

        #endregion


        public static List<EVendor_Visit_XMissionStructure> FindVisitsBetweenCoordinates(List<EVendor_Visit_XMissionStructure> visits, double maxLongitude, double maxLatitude, double minLongitude, double minLatiude)
        {
            List<EVendor_Visit_XMissionStructure> returnVisits = new List<EVendor_Visit_XMissionStructure>();

            foreach (EVendor_Visit_XMissionStructure visit in visits)
            {
                if(visit.Latitude <= maxLatitude && visit.Latitude >= minLatiude && visit.Longitude >= minLongitude && visit.Longitude <= maxLongitude)
                    returnVisits.Add(visit);
            }
            return returnVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingVendors(List<EVendor_Visit_XMissionStructure> visits, List<EVendor> vendors )
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                foreach (EVendor vendor in vendors)
                {
                    if (v.Vendor_Project.Id == vendor.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingContacts(List<EVendor_Visit_XMissionStructure> visits, List<EVendor_Contact> contacts)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                foreach (EVendor_Contact contact in contacts)
                {
                    if (v.Vendor_Contact.Id == contact.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingProjects(List<EVendor_Visit_XMissionStructure> visits, List<EVendor_Project> projects)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                foreach (EVendor_Project p in projects)
                {
                    if (v.Vendor_Project.Id == p.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingVisitTypes(List<EVendor_Visit_XMissionStructure> visits, List<EVendor_Visit_Type> visitTypes)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                foreach (EVendor_Visit_Type type in visitTypes)
                {
                    if (v.Vendor_Visit_Type.Vendor_Visit_TypeId == type.Vendor_Visit_TypeId)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingTopTypes(List<EVendor_Visit_XMissionStructure> visits, List<EXMissionStructures_TopType> topTypes)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                foreach (EXMissionStructures_TopType type in topTypes)
                {
                    if (v.TopType.XMissionStructures_TopTypeId == type.XMissionStructures_TopTypeId)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingPoleClasses(List<EVendor_Visit_XMissionStructure> visits, List<EXMissionStructures_PoleClass> classes)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                bool isMathing = false;
                foreach (EBasePole pole in v.Poles)
                {
                    foreach (EXMissionStructures_PoleClass poleClass in classes)
                    {
                        if(pole.Class.Id == poleClass.Id)
                        {
                            isMathing = true;
                        }
                    }
                }
                if(isMathing)
                    matchingVisits.Add(v);
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingPoleSpecies(List<EVendor_Visit_XMissionStructure> visits, List<EXMissionStructures_PoleSpecies> species)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                bool isMathing = false;
                foreach (EBasePole pole in v.Poles)
                {
                    foreach (EXMissionStructures_PoleSpecies poleSpecie in species)
                    {
                        if (pole.Species.Id == poleSpecie.Id)
                        {
                            isMathing = true;
                        }
                    }
                }
                if (isMathing)
                    matchingVisits.Add(v);
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingPoleTreatments(List<EVendor_Visit_XMissionStructure> visits, List<EXMissionStructures_PoleTreatment> treatments)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                bool isMathing = false;
                foreach (EBasePole pole in v.Poles)
                {
                    foreach (EXMissionStructures_PoleTreatment poleTreatment in treatments)
                    {
                        if (pole.Treatment.Id == poleTreatment.Id)
                        {
                            isMathing = true;
                        }
                    }
                }
                if (isMathing)
                    matchingVisits.Add(v);
            }
            return matchingVisits;
        }

        public static List<EVendor_Visit_XMissionStructure> MatchingPoleYears(List<EVendor_Visit_XMissionStructure> visits, List<Int32> years)
        {
            List<EVendor_Visit_XMissionStructure> matchingVisits = new List<EVendor_Visit_XMissionStructure>();
            foreach (EVendor_Visit_XMissionStructure v in visits)
            {
                bool isMathing = false;
                foreach (EBasePole pole in v.Poles)
                {
                    foreach (Int32 poleYear in years)
                    {
                        if (pole.Year == poleYear)
                        {
                            isMathing = true;
                        }
                    }
                }
                if (isMathing)
                    matchingVisits.Add(v);
            }
            return matchingVisits;
        }

        public static void SetAsset(EVendor_Visit_XMissionStructure structure, EUtility_Asset_XMissionStructure utility)
        {
            PPI.Client.Logic.Utility.LUtility_Asset_XMissionStructure.AssociateWithVisit(structure.Id, utility.Id);
        }

        public static List<EVendor_Visit_XMissionStructure> GetMobileProjectByPole(Int32 poleId, string connectionString)
        {
            StringBuilder _QueryMobile = new StringBuilder();
            _QueryMobile.AppendLine("Select ");
            _QueryMobile.AppendLine("   VI.VisitId as Vendor_Visit_XMissionStructureId,");
            _QueryMobile.AppendLine("   VI.AssetFk as UtilityAssetFk,");
            _QueryMobile.AppendLine("   VI.ProjectFk as Vendor_ProjectFK,");
            _QueryMobile.AppendLine("   VI.VisitId as StructureServerID,");
            _QueryMobile.AppendLine("   PRO.Name as vendor_Name,");

            _QueryMobile.AppendLine("   VI.VisitTypeFk as Vendor_Visit_TypeFK,");
            _QueryMobile.AppendLine("   VType.LookupText as visit_Name,");

            _QueryMobile.AppendLine("   VI.TopTypeFk as XMissionStructures_TopTypeFk,");
            _QueryMobile.AppendLine("   VI.TopTypeFk as Pole_TopID,");
            _QueryMobile.AppendLine("   TT.Name as TopType_Name, ");
            _QueryMobile.AppendLine("   TT.Name as Top_Name, ");

            _QueryMobile.AppendLine("   VI.Latitude as Structure_Latitude,");
            _QueryMobile.AppendLine("   VI.Longitude as Structure_Longitude,");
            _QueryMobile.AppendLine("   VI.Altitude as Structure_Altitude,");
            _QueryMobile.AppendLine("   VI.SatelliteCount as Structure_SatelliteCount,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Structure_SatelliteTime,");
            _QueryMobile.AppendLine("   VI.SolutionAvailable as Structure_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Structure_HaveImages,");
            _QueryMobile.AppendLine("   H.SettingValue as HandheldId,");
            _QueryMobile.AppendLine("   H.SettingName as HandheldName,");
            _QueryMobile.AppendLine("   VI.SatelliteTime as Date,");

            _QueryMobile.AppendLine("   P.PoleId as Pole_ID,");
            _QueryMobile.AppendLine("   P.Latitude as Pole_Latitude,");
            _QueryMobile.AppendLine("   P.Longitude as Pole_Longitude,");
            _QueryMobile.AppendLine("   P.Altitude as Pole_Altitude,");
            _QueryMobile.AppendLine("   P.SatelliteCount as Pole_SatelliteCount,");
            _QueryMobile.AppendLine("   P.SatelliteTime as Pole_SatelliteTime,");
            _QueryMobile.AppendLine("   P.SolutionAvailable as Pole_SolutionAvailable,");
            _QueryMobile.AppendLine("   0 as Pole_HaveImages,");
            _QueryMobile.AppendLine("   0 as UploadedImages,");
            _QueryMobile.AppendLine("   0 as TotalImages,");
            _QueryMobile.AppendLine("   P.Year as Pole_Year,");
            _QueryMobile.AppendLine("   P.AID as Pole_AID,");
            _QueryMobile.AppendLine("   P.LVisit as Pole_LVisit,");
            _QueryMobile.AppendLine("   P.Number as Pole_Number,");
            _QueryMobile.AppendLine("   P.Length as Pole_Length,");
            _QueryMobile.AppendLine("   P.PoleId as Pole_PoleServerID,");
            _QueryMobile.AppendLine("   P.LVisitor as Pole_LVisitor,");

            _QueryMobile.AppendLine("   P.SpeciesFk as Pole_SpeciesID,");
            _QueryMobile.AppendLine("   S.LookupText as Species_Name,");

            _QueryMobile.AppendLine("   P.TreatmentFk as Pole_TreatmentID,");
            _QueryMobile.AppendLine("   T.LookupText as Treatment_Name,");

            _QueryMobile.AppendLine("   P.ClassFk as Pole_ClassID,");
            _QueryMobile.AppendLine("   C.LookupText as Classes_Name,");

            _QueryMobile.AppendLine("   V.VendorId as Vendor_Id,");
            _QueryMobile.AppendLine("   V.Name as Company_Name,");

            _QueryMobile.AppendLine("   U.UserId as vendor_Contact_Id,");
            _QueryMobile.AppendLine("   U.Name as Vendor_Contact_Name ");


            _QueryMobile.AppendLine("From ");
            _QueryMobile.AppendLine("   tb_Vendor_Visits AS VI");
            //_QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingData AS D ON D.VisitFk = VI.VisitId");
            _QueryMobile.AppendLine("   INNER JOIN tb_Vendor_Visit_XStructureMappingPoles AS P ON P.VisitFk = VI.VisitId");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Tops as TT ON VI.TopTypeFk = TT.TopId ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as S ON S.LookupValue = P.SpeciesFk AND S.LookupTypeFk ='Species'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as T ON T.LookupValue = P.TreatmentFk AND T.LookupTypeFk ='Treatments'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as C ON C.LookupValue = P.ClassFk AND C.LookupTypeFk ='Classes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Lookups as VType ON VType.LookupValue = VI.VisitTypeFk AND VType.LookupTypeFk ='VisitTypes'");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Settings as H ON H.SettingValue = VI.Handheld AND H.SettingKey =" + (Int32)EnumMobileSettings.Handheld + "");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Projects as PRO ON PRO.ProjectId = VI.ProjectFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_vendor as V ON V.VendorId = VI.VendorFk ");
            _QueryMobile.AppendLine("   LEFT JOIN tb_Users as U ON U.UserId = VI.UserFk ");
            _QueryMobile.AppendLine(" Where (P.PoleId = @PoleId)");
            _QueryMobile.AppendLine(" Order by VI.VisitId");


            List<SqlParameter> listParameters = new List<SqlParameter>();

            listParameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, poleId));
            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_QueryMobile.ToString(), connectionString, listParameters.ToArray()).Tables[0];

            return ParseListProjectVisits(dt, connectionString);


        }
    }
}
